﻿
using System;
using System.Data.SQLite;

namespace Atest
{
    internal class Program
    {
        static void Main(string[] args)
        {

            Init();

            Console.ReadKey();
        }

        private static void Init()
        {
            // 指定路径 创建SQLite数据库
            //var dataFile = "D:\\SQLite\\Test.db";
            //SQLiteConnection.CreateFile(dataFile);
            //SQLiteConnection db = new SQLiteConnection();
            //SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
            //{
            //    DataSource = dataFile,
            //    Password = null
            //};
            //db.ConnectionString = conStr.ToString();
            //db.Open();

            // 创建SQLite数据库
            var db = new SQLiteConnection("Data Source=test.db");
            db.Open();       

            
            var Cmd = new SQLiteCommand();

            // 创建表
            CreateTable(Cmd, db);

            // 插入数据
            for (int i = 0; i < 10; i++)
            {
                var tr = GenerateThreeRandomCharacters();
                InsetDATA(Cmd, db, tr);
            }


            // 查询数据
            SelectDATA(Cmd, db);

            // 更新数据
            UpdateDATA(Cmd, db);

            // 删除数据
            DeleteData(Cmd, db);

            db.Close();
        }

        //
        private static void DeleteData(SQLiteCommand cmd,SQLiteConnection db)
        {          
            cmd.CommandText = "DELETE FROM users WHERE id = @id";
            cmd.Connection = db;
            cmd.Parameters.AddWithValue("@id", 5);
            cmd.ExecuteNonQuery();
        }

        private static void UpdateDATA(SQLiteCommand cmd,SQLiteConnection db)
        {
            cmd.CommandText = "UPDATE users SET name = @newName WHERE id = @id";
            cmd.Connection = db;
            cmd.Parameters.AddWithValue("@newName", "Jane Doe");
            cmd.Parameters.AddWithValue("@id", 10);
            cmd.ExecuteNonQuery();
        }

        const string Select = "SELECT * FROM";
        //查询数据
        private static void SelectDATA(SQLiteCommand cmd, SQLiteConnection db)
        {
            cmd.CommandText = $"{Select} {table} WHERE id = @id";
            cmd.Connection = db;
            cmd.Parameters.AddWithValue("@id", 3);


            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("ID: {0}, Name: {1}", reader.GetInt32(0), reader.GetString(1));
                }
            }
        }

        const string creatTable = "CREATE TABLE IF NOT EXISTS";
        const string key = "(id INTEGER PRIMARY KEY , name TEXT)";
        //创建表   
        private static void CreateTable(SQLiteCommand cmd, SQLiteConnection db)
        {
            cmd.CommandText = $"{creatTable} {table} {key}";
            cmd.Connection = db;
            cmd.ExecuteNonQuery();
        }

        const string add = "INSERT INTO";
        const string table = "users";
        //添加数据
        private static void InsetDATA(SQLiteCommand cmd, SQLiteConnection db, string name)
        {
            cmd.CommandText = $"{add} {table} (name) VALUES (@name)";
            cmd.Connection = db;
            cmd.Parameters.AddWithValue("@name", name);
            cmd.ExecuteNonQuery();
        }


       static Random random = new Random();
        static string GenerateThreeRandomCharacters()
        {
            char[] characters = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z' };
         
            string result = "";
            for (int i = 0; i < 3; i++)
            {
                int randomIndex = random.Next(characters.Length);
                result += characters[randomIndex];
            }
            Console.WriteLine("随机的 3 位字符: " + result);
            return result;
        }

      
    }
}
